﻿create database tbl
on
( name='tbl',
  filename='D:\tbl.mdf'


)
log on
( name='tbl_log',
  filename='D:\tbl_log.ldf'



)
go
use tbl
create table tbl_card
(
    id varchar(20) primary key  not null,
	passWord nvarchar(20) not null,
	balance int not null ,
	userName nvarchar(20) not null


)
create table tbl_computer
(
     id varchar(20) primary key not null  ,
	 onUse varchar(2) check(onUse in ('0','1')) not null,
	 note nvarchar(20)



 )
 create table tbl_record
 (  
      id varchar(20) primary key not null,
	  cardId  varchar(20) references tbl_card(id),
	  ComputerId  varchar(20) references tbl_computer(id),
	  beginTime datetime ,
	  endTime datetime,
	  fee int 


 
 
 )
 insert into tbl_card values 
 ('0023_ABC','555','98','蠟엊'),
 ('0025_bbd','abe','300','聾에'),
 ('0036_CCD','부진','100','부진'),
 ('0045_YGR','0045_YGR','58','聯駱'),
 ('0078_RJV','55885fg','600','叫헤'),
 ('0089_EDE','zhang','134','蠟엎')
 select * from tbl_card
 insert into tbl_computer values('02','0','25555'),
 ('03','1','55555'),
 ('04','0','66666'),
 ('05','1','88888'),
 ('06','0','688878'),
 ('B01','0','558558')
 select * from tbl_computer
 insert into tbl_record values
 ('23','0078_RJV','B01','2007-07-15 19:00:00','2007-07-15 21:00:00','20'),
 ('34','0025_bbd','02','2006-12-25 18:00:00','2006-12-25 22:00:00','23'),
 ('45','0023_ABC','03','2006-12-23 15:26:00','2006-12-23 22:55:00','50'),
 ('46','0023_ABC','03','2006-12-22 15:26:00','2006-12-22 22:55:00','6'),
 ('47','0023_ABC','03','2006-12-23 15:26:00','2006-12-23 22:55:00','50'),
 ('48','0023_ABC','03','2007-01-06 15:26:00','2007-01-06 22:55:00','6'),
 ('55','0023_ABC','03','2006-07-21 15:26:00','2006-07-21 22:55:00','50'),
 ('64','0045_YGR','04','2006-12-24 18:00:00','2006-12-24 22:00:00','30'),
 ('65','0025_bbd','02','2006-12-28 18:00:00','2006-12-28 22:00:00','23'),
 ('98','0025_bbd','02','2006-12-26 18:00:00','2006-12-26 22:00:00','23')
 select * from tbl_record

 --1. 查询出用户名为'张军'的上网卡的上网记录，要求显示卡号，用户名，机器编号、开始时间、结束时间，和消费金额，并按消费金额降序排列
		select C.id 卡号, userName 用户名,  CP.id 机器编号 , beginTime 开始时间, endTime 结束时间 ,fee 消费金额 
		from tbl_card C,tbl_computer CP ,tbl_record R 
		where C.id=R.cardId and CP.id=R.ComputerId and userName='张军' 
		order by fee DESC

--2. 查询出每台机器上的上网次数和消费的总金额
		select CP.Id 机器编号, count(ComputerId) 上网次数, sum(fee) 消费总金额 from tbl_computer CP 
		left join tbl_record R on R.ComputerId =CP.id 
		group by CP.Id 
--3. 查询出所有已经使用过的上网卡的消费总金额
		select cardId 网卡号,sum(fee) 消费总金额 
		from tbl_record  
		group by cardId
		--4. 查询出从未消费过的上网卡的卡号和用户名

select C.id 卡号, userName 用户名 from tbl_card C 
		left join tbl_record R on C.id=R.cardId  
		where fee is null

--5. 将密码与用户名一样的上网卡信息查询出来

		select  * from tbl_card 
		where passWord= userName

--6. 查询出使用次数最多的机器号和使用次数

		select top 1 ComputerId 使用次数最多的机器号, count(ComputerId) 使用次数 from tbl_record  
		group by ComputerId  
		order by count(ComputerId) DESC

--7. 查询出卡号是以'ABC'结尾的卡号，用户名，上网的机器号和消费金额

		select C.id 卡号, userName 用户名, R.ComputerId  上网机器编号 ,fee 消费金额 from tbl_card C 
		left join tbl_record R on C.id = R.cardId
		where C.id  like '%ABC'

--8. 查询出是周六、周天上网的记录，要求显示上网的卡号，用户名，机器号，开始时间、结束时间和消费金额

		select R.cardId 卡号, userName 用户名, ComputerId 机器号, beginTime 开始时间, endTime 结束时间  from tbl_record R 
		left join tbl_card C on R.cardId=C.id 
		where datename(DW,beginTime)='星期六' or datename(DW,beginTime)='星期天'

--9. 查询成一次上网时间超过12小时的的上网记录，要求显示上网的卡号，用户名，机器号，开始时间、结束时间和消费金额

		select R.cardId 卡号, userName 用户名, ComputerId 机器号, beginTime 开始时间 ,endTime 结束时间 ,fee 消费金额 from tbl_record R 
		left join tbl_card C on  R.cardId= C.id 
		where datediff(HH,beginTime,endTime)>12

--10. 查询除消费金额排列前三名(最高)的上网记录，要求显示上网的卡号，用户名，机器号，开始时间、结束时间和消费金额

		select top 7 cardId 卡号, userName 用户名,  ComputerId 机器号, beginTime 开始时间,endTime 结束时间 , fee 消费金额  from tbl_record 
		left join tbl_card C on cardId=C.id  
		order by fee